import xlrd
import pymysql
#打开数据所在的工作簿，以及选择存有数据的工作表
book = xlrd.open_workbook(r'C:\Users\Administrator\Desktop\1_FOX sample.xlsx')
sheet = book.sheet_by_index(4)
#建立一个MySQL连接
conn = pymysql.connect(
        host='localhost',
        user='root',
        passwd='123456',
        db = 'homework',
        port=3306,
        charset='utf8'
        )
# 获得游标
cur = conn.cursor()

# SQL 插入语句
# sql = "CREATE TABLE IF NOT EXISTS EQP_Master(" \
#       "EQPID varchar(100),EQPGroup varchar(100),MFG_EQPGroup varchar(100),Chamber_List varchar(100),Batch_Size int," \
#       "Batch_Cnt int,Load_Port_List varchar(100),Module varchar(10),Vendor_Type varchar(10),PMI_flag varchar(100),RB_GroupINT varchar(100))"

# sql = "CREATE TABLE IF NOT EXISTS WPH(EQPID varchar(100),RECIPE varchar(100),WPH int)"
# conn.commit()
# sql = "CREATE TABLE IF NOT EXISTS ELR(LOTID varchar(100),FOX_RECIPE varchar (100),EQPID varchar(100),BatchSize int)"

# sql = "CREATE TABLE IF NOT EXISTS Curret_Wip(LOTID varchar(100),STATUS varchar(100),PRODUCTNAME varchar(100),STEPNO varchar(100),RECIPE varchar(100),FOX_RECIPE varchar(100),TECHNOLOGY varchar(100),STAGE varchar(100),RESOURCETYPE varchar(100),WIP_Flag varchar(100)," \
#       "PRIORITY varchar(100),InternalPriority varchar(100),COMPONENTQTY varchar(100),REMIANQTIME varchar(100),TRACKOUTTIME varchar(100)," \
#       "QUEUETIME varchar(100),MFGEQPGROUP varchar(100),EQPID varchar(100),RUNTIME varchar(100),PROCESS_START_TIME varchar(100),LOTCHAMBERLIST varchar(100)," \
#       "DUEDATE varchar(100),LOCATION varchar(100),FABLOTTYPE varchar(100))"

# sql = "CREATE TABLE IF NOT EXISTS EQP_Status(EQUIPMENT_ID varchar(100) ,CURRENT_STATUS varchar(100))"
# cur.execute(sql)


w = "INSERT INTO EQP_Status VALUES (%s,%s)"
for i in range(1,sheet.nrows):    # sheet.nrows  获取工作表的行数      table.ncols 列数
    id = sheet.cell(i,0).value
    name = sheet.cell(i,1).value
    # age = sheet.cell(i,2).value
    values = (id,name)
    cur.execute(w,values)
conn.commit()
cur.close()
conn.close()
